package com.advertisementsystem.Mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
@Mapper
public interface DeviceIncomeMapper {

    /**
     * 获取设备总收入
     */
    @Select("SELECT IFNULL(SUM(b.amount), 0) " +
            "FROM bill b " +
            "JOIN ad_negotiation an ON b.negotiation_id = an.id " +
            "WHERE an.device_id = #{deviceId} AND b.status = 'PAID'")
    BigDecimal getDeviceTotalIncome(@Param("deviceId") Long deviceId);

    /**
     * 获取设备月收入
     */
    @Select("SELECT IFNULL(SUM(b.amount), 0) " +
            "FROM bill b " +
            "JOIN ad_negotiation an ON b.negotiation_id = an.id " +
            "WHERE an.device_id = #{deviceId} AND b.status = 'PAID' " +
            "AND YEAR(b.payment_date) = YEAR(CURRENT_DATE) " +
            "AND MONTH(b.payment_date) = MONTH(CURRENT_DATE)")
    BigDecimal getDeviceMonthlyIncome(@Param("deviceId") Long deviceId);

    /**
     * 获取设备最近7天的收入
     */
    @Select("SELECT DATE_FORMAT(b.payment_date, '%Y-%m-%d') as date, IFNULL(SUM(b.amount), 0) as amount " +
            "FROM bill b " +
            "JOIN ad_negotiation an ON b.negotiation_id = an.id " +
            "WHERE an.device_id = #{deviceId} AND b.status = 'PAID' " +
            "AND b.payment_date BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY) AND CURRENT_DATE " +
            "GROUP BY DATE_FORMAT(b.payment_date, '%Y-%m-%d') " +
            "ORDER BY date ASC")
    List<Map<String, Object>> getDeviceWeeklyIncome(@Param("deviceId") Long deviceId);

    /**
     * 获取设备广告排期
     */
    @Select("SELECT s.id, a.title, s.start_time, s.end_time, s.status, " +
            "IFNULL(s.cost_per_mille * s.impressions / 1000, 0) as revenue " +
            "FROM ad_schedule s " +
            "JOIN advertisement a ON s.advertisement_id = a.id " +
            "WHERE s.device_id = #{deviceId} AND s.is_deleted = 0 " +
            "ORDER BY s.start_time DESC " +
            "LIMIT #{limit} OFFSET #{offset}")
    List<Map<String, Object>> getDeviceAdSchedules(
            @Param("deviceId") Long deviceId,
            @Param("limit") int limit,
            @Param("offset") int offset);

    /**
     * 获取设备广告排期总数
     */
    @Select("SELECT COUNT(*) FROM ad_schedule WHERE device_id = #{deviceId} AND is_deleted = 0")
    Integer countDeviceAdSchedules(@Param("deviceId") Long deviceId);
}